--处理替换关系
CREATE OR REPLACE PROCEDURE
DEAL_SUPERSESSION AUTHID CURRENT_USER
IS

BEGIN
  /**********   PDS_DIRS_HISTORYLIST数据处理    **********/
  UPDATE PDS_DIRS_HISTORYLIST
  SET
    TYPEID = UPPER(TRIM(TYPEID)),
    SERVICECODE = UPPER(TRIM(SERVICECODE)),
    OLDPARTNO = UPPER(TRIM(OLDPARTNO)),
    IA = UPPER(TRIM(IA)),
    SERIALNO = TRIM(SERIALNO),
    NOTE = TRIM(NOTE);

  UPDATE PDS_DIRS_HISTORYLIST
  SET
    TYPEID = SUBSTR(TYPEID, 2)
  WHERE REGEXP_LIKE(TYPEID, '^\(.*');

  UPDATE PDS_DIRS_HISTORYLIST
  SET
    TYPEID = SUBSTR(TYPEID, 1, INSTR(TYPEID, '(') - 1)
  WHERE TYPEID LIKE '%(%';

  UPDATE PDS_DIRS_HISTORYLIST
  SET
    TYPEID = SUBSTR(TYPEID, 1, INSTR(TYPEID, '（') - 1)
  WHERE TYPEID LIKE '%（%';

  UPDATE PDS_DIRS_HISTORYLIST
  SET
    OLDPARTNO = NULL
  WHERE OLDPARTNO IN ('/', '／', '\', '更改前号', '-');

  UPDATE PDS_DIRS_HISTORYLIST
  SET
    SERVICECODE = NULL
  WHERE SERVICECODE IN ('/', '／', '\', '更改后号', '-');

  --这里的每条记录是一个单行替换(每条记录是一个替换组)，所以只选取父配件、替换前子、替换后子都不为空的记录
  EXECUTE IMMEDIATE 'TRUNCATE TABLE FULL_HISTORYLIST';

  INSERT INTO FULL_HISTORYLIST
  SELECT
    TYPEID,
    OLDPARTNO,
    SERIALNO,
    IA,
    SERVICECODE,
    NOTE
  FROM (
         SELECT
           D.*,
           ROW_NUMBER() OVER (PARTITION BY TYPEID, OLDPARTNO, SERVICECODE ORDER BY ID DESC) RANK
         FROM PDS_DIRS_HISTORYLIST D
         WHERE TYPEID IS NOT NULL AND OLDPARTNO IS NOT NULL AND SERVICECODE IS NOT NULL
       )
  WHERE RANK = 1;


  /**********   PDS_PARTS_HISTORYLIST数据处理    **********/
  UPDATE PDS_PARTS_HISTORYLIST
  SET
    PARTNO = UPPER(TRIM(PARTNO)),
    SERVICECODE = UPPER(TRIM(SERVICECODE)),
    OLDPARTNO = UPPER(TRIM(OLDPARTNO)),
    IA = UPPER(TRIM(IA)),
    SERIALNO = TRIM(SERIALNO),
    NOTE = TRIM(NOTE);

  UPDATE PDS_PARTS_HISTORYLIST
  SET
    PARTNO = SUBSTR(PARTNO, 2)
  WHERE REGEXP_LIKE(PARTNO, '^\(.*');

  UPDATE PDS_PARTS_HISTORYLIST
  SET
    OLDPARTNO = NULL
  WHERE OLDPARTNO IN ('/', '／', '\', '更改前号', '-');

  UPDATE PDS_PARTS_HISTORYLIST
  SET
    SERVICECODE = NULL
  WHERE SERVICECODE IN ('/', '／', '\', '更改后号', '-');

  --去除父配件号中'('、'（'之后的内容
  UPDATE PDS_PARTS_HISTORYLIST
  SET
    PARTNO = SUBSTR(PARTNO, 1, INSTR(PARTNO, '(') - 1)
  WHERE PARTNO LIKE '%(%';

  UPDATE PDS_PARTS_HISTORYLIST
  SET
    PARTNO = SUBSTR(PARTNO, 1, INSTR(PARTNO, '（') - 1)
  WHERE PARTNO LIKE '%（%';

  --这里的每条记录是一个单行替换(每条记录是一个替换组)，所以只选取父配件、替换前子、替换后子都不为空的记录
  INSERT INTO FULL_HISTORYLIST
  SELECT
    PARTNO,
    OLDPARTNO,
    SERIALNO,
    IA,
    SERVICECODE,
    NOTE
  FROM (
         SELECT
           D.*,
           ROW_NUMBER() OVER (PARTITION BY PARTNO, OLDPARTNO, SERVICECODE ORDER BY ID DESC) RANK
         FROM PDS_PARTS_HISTORYLIST D
         WHERE PARTNO IS NOT NULL AND OLDPARTNO IS NOT NULL AND SERVICECODE IS NOT NULL
       )
  WHERE RANK = 1;


  /**********   FULL_HISTORYLIST数据处理    **********/
  --ECN_NO对应变更编号，只有'EC'、'KS'打头的才是有效的，其余转成空值
  UPDATE FULL_HISTORYLIST SET ECN_NO = NULL WHERE ECN_NO NOT LIKE 'EC%' AND ECN_NO NOT LIKE 'KS%';

  --有些记录的BP_INFO中存在变更编号，如果该记录的ECN_NO为空，则将BP_INFO变更编号放入
  UPDATE FULL_HISTORYLIST SET ECN_NO = BP_INFO WHERE ECN_NO IS NULL AND (BP_INFO LIKE 'EC%' OR BP_INFO LIKE 'KS%');

  UPDATE FULL_HISTORYLIST SET BP_INFO = REPLACE(BP_INFO, 'SN', 'S/N') WHERE BP_INFO LIKE 'SN%';

  UPDATE FULL_HISTORYLIST SET BP_INFO = NULL WHERE BP_INFO NOT LIKE 'S/N%';

  --处理互换性
  UPDATE FULL_HISTORYLIST SET TYPE_CODE = SUBSTR(TYPE_CODE, 1, INSTR(TYPE_CODE, ',') - 1) WHERE REGEXP_LIKE(TYPE_CODE, '^\w{1,2},.*$');

  UPDATE FULL_HISTORYLIST SET TYPE_CODE = 'I' WHERE TYPE_CODE IN ('1', 'P3');

  UPDATE FULL_HISTORYLIST SET TYPE_CODE = 'N' WHERE TYPE_CODE IN ('N1', 'N2', '83A0426', 'S/N 293732-UP');

  UPDATE FULL_HISTORYLIST SET TYPE_CODE = 'P' WHERE TYPE_CODE IN ('J', '无互换性', '无', '设计整图');

  UPDATE FULL_HISTORYLIST SET TYPE_CODE = 'TN' WHERE TYPE_CODE IN ('F1 X', 'X');

  UPDATE FULL_HISTORYLIST SET TYPE_CODE = 'TP' WHERE TYPE_CODE IN ('D1');

  UPDATE FULL_HISTORYLIST SET TYPE_CODE = 'TY' WHERE TYPE_CODE IN ('F1' , 'F2', 'NY');

  UPDATE FULL_HISTORYLIST SET TYPE_CODE = 'Y' WHERE TYPE_CODE IN ('C', 'C1', '40C0635', '40C1333', '有', '2D转3D');


  /**********   构建USEFUL_HISTORYLIST、ERROR_HISTORYLIST    **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE USEFUL_HISTORYLIST';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ERROR_HISTORYLIST';

  INSERT INTO USEFUL_HISTORYLIST
  SELECT
    T.*
  FROM FULL_HISTORYLIST T
  LEFT JOIN ACT A1 ON A1.PARENT_PART_NO = T.PARENT_PART_NO AND A1.PART_NO = T.PART_NO_BEFORE
  LEFT JOIN ACT A2 ON A2.PARENT_PART_NO = T.PARENT_PART_NO AND A2.PART_NO = T.PART_NO_AFTER
  WHERE A1.PART_NO IS NOT NULL AND A2.PART_NO IS NOT NULL;

  INSERT INTO ERROR_HISTORYLIST
  SELECT
    T1.*
  FROM FULL_HISTORYLIST T1
  LEFT JOIN USEFUL_HISTORYLIST T2 ON T2.PARENT_PART_NO = T1.PARENT_PART_NO AND T2.PART_NO_BEFORE = T1.PART_NO_BEFORE AND T2.PART_NO_AFTER = T1.PART_NO_AFTER
  WHERE T2.PARENT_PART_NO IS NULL;


  /**********   构建S_ACT_NOT_EXISTS    **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE S_ACT_NOT_EXISTS';

  INSERT INTO S_ACT_NOT_EXISTS
  SELECT
    T.*,
    PRO.NAME_ZH
  FROM
  (
    SELECT
      PARENT_PART_NO,
      PART_NO_BEFORE PART_NO
    FROM ERROR_HISTORYLIST
    UNION
    SELECT
      PARENT_PART_NO,
      PART_NO_AFTER PART_NO
    FROM ERROR_HISTORYLIST
  ) T
  LEFT JOIN ACT A ON A.PARENT_PART_NO = T.PARENT_PART_NO AND A.PART_NO = T.PART_NO
  LEFT JOIN PART P ON P.PART_NO = T.PARENT_PART_NO
  LEFT JOIN MODEL M ON M.CODE = P.MODEL_CODE
  LEFT JOIN SUBMACHINE SM ON SM.CODE = M.SUBMACHINE_CODE
  LEFT JOIN MACHINE MC ON MC.CODE = SM.MACHINE_CODE
  LEFT JOIN PRODUCT PRO ON PRO.CODE = MC.PRODUCT_CODE
  WHERE A.PART_NO IS NULL;


  /**********   构建PDSN_SUPERSESSION   **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE PDSN_SUPERSESSION';

  INSERT INTO PDSN_SUPERSESSION
  (
    CODE, 
    TYPE_CODE, 
  	CHANGE_CODE,
    BP_INFO,
    CREATED_BY,
    CREATED_DATE
  )
  SELECT
    PARENT_PART_NO || '_' || PART_NO_BEFORE || '_' || PART_NO_AFTER,
    TYPE_CODE,
  	ECN_NO,
    BP_INFO,
    'PDS',
    SYSDATE
  FROM USEFUL_HISTORYLIST;


  /**********   构建PDSN_SUPERSESSION_DETAIL   **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE PDSN_SUPERSESSION_DETAIL';

  --替换前信息
  INSERT INTO PDSN_SUPERSESSION_DETAIL
  (
    SUPERSESSION_CODE, 
    IS_OLD, 
    PARENT_PART_NO, 
    PART_NO,
    CREATED_BY,
    CREATED_DATE
  )
  SELECT
    PARENT_PART_NO || '_' || PART_NO_BEFORE || '_' || PART_NO_AFTER,
    1, 
    PARENT_PART_NO,
  	PART_NO_BEFORE,
    'PDS',
    SYSDATE
  FROM USEFUL_HISTORYLIST;

  --替换后信息
  INSERT INTO PDSN_SUPERSESSION_DETAIL
  (
    SUPERSESSION_CODE, 
    IS_OLD, 
    PARENT_PART_NO, 
    PART_NO,
    CREATED_BY,
    CREATED_DATE
  )
  SELECT
    PARENT_PART_NO || '_' || PART_NO_BEFORE || '_' || PART_NO_AFTER,
    0, 
    PARENT_PART_NO,
    PART_NO_AFTER,
    'PDS',
    SYSDATE
  FROM USEFUL_HISTORYLIST;

  COMMIT;

END;